oracle10G内存设置多大,oracle10g内存管理(转)

Oracle10g动态内存管理：

内存结构是oracle体系结构中最重要的一个部分之一。按照系统对内存使用方法的不同，可以分为系统全局区(SGA)、程序全局区(PGA)、排序区(Sort

Area)、大池(Large Pool)、及java池(java Pool)，以下分别加以说明：

系统全局区(System Global Area)

它是一组为系统分配的内存共享结构，可以包含一个数据库实例的数据和控制信息。如果多个用户连接到一个实例，在实例的系统全局区中，数据可以被多个用户共享，所以又称共享全局区。系统全局区按其作用不同，可以分为数据缓冲区、日志缓冲区及共享池。

1 数据缓冲区：

数据缓冲区用于从磁盘读入的数据，供所有用户共享。

修改的数据、插入的数据存储在数据缓冲区中，修改完成或DBWR进程的其他条件引发时，数据被写入数据文件

数据缓冲区工作原理

LRU (Least recently

used)：最近最少使用原则的缩写，是一种数据缓冲区的一种管理机制，，只保留最近数据，不保留旧数据。

Dirty：表示脏数据，脏数据是修改后还没有写到数据文件的数据。

Oracle10g

的数据库内存的设置参数不再由DB_BLOCK_BUFFERS确定，而是由oracle的新参数DB_CACHE_SIZE

和DB_nK_CACHE_SIZE确定，不同的数据段可以使用不同的数据块。大表可以存储在大的数据块表空间中，小表可以存储在小的数据块表空间中，以优化i/o性能。对于系统表空间、临时表空间、及其它默认设置的表空间，可以使用标准的数据块DB_BLOCK_SIZE确定。

Oracle10g数据库缓冲区参数与数据块对应关系数

BLOCKSIZE

数据缓冲区大小设置

DB_BLOCK_SIZE

数据缓冲区大小设置

2k

DB_2K_CACHE_SIZE

2k

DB_CACHE_SIZE

4K

DB_4K_CACHE_SIZE

4K

DB_CACHE_SIZE

8K

DB_8K_CACHE_SIZE

8K

DB_CACHE_SIZE

16K

DB_16K_CACHE_SIZE

16K

DB_CACHE_SIZE

32K

DB_32K_CACHE_SIZE

32K

DB_CACHE_SIZE

标准数据块DB_BLOCK_SIZE用于系统表空间及默认表空间，其他表空间可以使用非标准数据块BLOCKSIZE(创建表空间时使用)，其值分别为

2k 4k 8k 16k 32k ，非标准数据块的数据缓冲区使用参数DB_Nk_CACHE_SIZE确定。

需要注意的是BLOCKSIZE不得用于标准块。如果设置了DB_BLOCK_SIZE=2048,则不得设置DB_2K_CACHE_SIZE，标准块必须使用参数DB_CACHE_SIZE

来设置。

同时可以在线修改数据缓冲区参数

SQL> alter system set db_2k_cache_size = 10M ;

如果要查询数据缓冲区大小，可以如下：

SQL> show parameter db

在创建不同数据块表空间时，要使用参数BLOCKSIZE 指出数据块的大小，同时在参数文件中要使用 DB_Nk_CACHE_SIZE

进行配置，与BLOCKSIZE的个数相对应，否则会出现错误。

设置动态内存时，可以将多个参数全部写入参数文件，格式如下:

# cache and i/o

DB_BLOCK_SIZE=4096

DB_CACHE_SIZE=20971520

DB_2K_CACHE_SIZE=8M

DB_8K_CACHE_SIZE=4M

……..

其中，参数 DB_CACHE_SIZE

只适用于系统表空间、临时表空间、及默认表空间，DB_2K_CACHE_SIZE 适合

BLOCKSIZE 为2K的表空间。8K 也是一样的道理。

数据缓冲区对数据库德存取速度又直接影响。一般的缓冲区命中率应该在90% 以上。例如，使用数据字典 v$sysstat

计算数据缓冲区命中率：

SQL> select a.value+b.value “logical_reads” ,

c.value “phys_reads”,

Round(100* ( ( a.value+b.value)- c.value) /

( a.value+b.value ) ) “buffer hit radio “

From v$sysstat a, v$sysstat b,v$sysstat c

Where a.statistic#=38 and b.statistic#=39 and c.statistic#=40;

下面是计算数据缓冲命中率的另一种方法：

SQL> select name, value

From v$sysstat

Where name in ( ‘session logical reads’,’physical reads’,physical

reads direct’, ‘physical reads direct (lob)’);

其中

Session logical reads 为读的总量。

Physical reads为从数据文件读

Physical reads direct 为从缓冲区读(不含lobs)

Physical reads direct (lobs) 为从缓冲区读(含lobs)

Hit Ratio = 1- ( ( physical reads- physical reads direct – physical

reads direct(lob) ) /session logical reads) = 95%

日志缓冲区

日志缓冲区用来存储数据库的修改信息。日志信息首先在日志缓冲区中产生，当日志缓冲区的日志达到一定数量时，由日志写入进程LGWR将日志数据写入日志文件组，再经过切换，由归档进程ARCH将日志数据写入归档介质。

日志缓冲区大小由参数LOG_BUFFER确定，要查询日志缓冲区大小可以用以下方法：

方法一：参数文件中：

……

Processes = 150

Parallel_max_servers = 5

Log_buffer = 32768

……..

方法二：

SQL> select name,value from

v$parameter where name like ‘%buffer’;

方法三：

SQL> show parameter log_buffer

对于日志缓冲区而言可以计算失败率，使用数据字典v$latch 计算日志缓冲区的失败率

SQL>select

name,gets,misses,immediate_gets,immediate_misses,

Decode(gets,0,0,misses/gets*100) ratiol,

Decode (immediate_gets+immediate_misses,0,0,

immediate_misses/(immediate_gets+immediate_misses)*100) ratio2

from v$latch

where name in (‘redo allocation’, ‘redo copy’);

其中

Gets 表示成功等待日志缓冲区的次数

Immediate gets 表示成功立即得到日志缓冲区的次数。

Immediate misses 表示未成功立即得到日志缓冲区的次数。

等待表示日志在进入日志缓冲区时，因为日志缓冲区过小而没有空闲空间，所以日志缓冲区的失败可以表示日志缓冲区是否足够大，不够大时，用户的日志写将产生等待过程。日志缓冲区的失败率应该小于1%

此外，可以查询用户进程等待日志缓冲区时的次数，通过数据字典v$sysstat 得到：

SQL> select name,value from v$sysstat

Where name = ‘ redo buffer allocation retries’ ;

共享池

SGA中的第三个组成部分是共享池。共享池是对sql ,pl/sql

程序进行语法分析、编译、执行的内存区域。共享池包括库缓冲区(library cache)、数据字典缓冲区(Data Directory

Cache)

用户全局区(User Global Area)。其中库缓冲区含有Sql

语句的分析码、执行计划；数据字典缓冲区含有从数据字典中得到的表、列定义、权限。用户全局区包含用户的MTS 会话信息。

共享池主要用于对SQL 、pl/sql

程序语句进行语法分析、编译、执行、所以，如果应用中药运行大量存储过程或包，则要增加共享池的尺寸。共享池的大小由参数SHARE_POOL_SIZE确定。要了解共享池大小，可以用以下方法：

方法一：参数文件

………

Shared_Pool_size = 52428800

…..

方法二：

SQL> select name,value from v$parameter where name

like ‘%size’;

方法三：

SQL> show parameter share_pool_size

共享池应计算存储过程、包等的成功率

可以查询数据字典 v$rowcache 了解数据字典的成功与失败次数

SQL> select sum(gets) “dictionary gets”,

Sum(getmisses) “dictionary cache getmisses”

From v$rowcache ;

其中 gets 表示读取某一类数据字典的成功次数，getsmisses 表示读取某一类数据字典的失败次数。

此外还可以通过查询结果计算共享池中读取数据字典的成功率

SQL> select parameter, get, getmisses,

getmisses/(getmisses+gets)*100 “miss ratio”,

(1- (sum(getmisses)/(sum(getmisses)+sum(gets)) ) ) *100 “hit ratio”

From v$rowcache

Where gets+getmisses0

Group by parameter,gets,getmisses;

查询数据字典 v$librarycache 可以计算共享池中库缓存的失败率，结果应该小于1%

SQL>select sum(pins) “ total pins”, sum(reloads) “

total reloads”, sum(reloads)/sum(pins)*100

libarycache from v$librarycache;

其中 total pins 表示驻留内存的次数， total reloads 表示重新加载到内存的次数，librarycache

表示失败率。

上面分析了系统全局区的三个组成部分-----数据缓冲区、日志缓冲区及共享池，如果要得到SGA的总大小，可以在SQL*Plus中使用show

sga 命令

SQL>show sga

或查询数据字典

SQL> select * from v$sga;

如果要查询某个参数的大小，可以查询数据字典v_$sagstat,通过计算可以知道sga的使用空间与空闲空间的比。

SQL>col OBJECT_NAME format a20

SQL> col 空闲空间百分比(%) format 90.99

SQL> select name,

Sgasize/1024/1024 “allocated(M)” ,

Bytes/1024 “空闲空间(k)” ,

Round(bytes/sagsize*100,2) “空闲空间百分比(%)”

From ( select sum(bytes) sgasize from sys.v_$sgastat) s

,sys.v_$sgastat f

Where

f.name=’free memory’

;

程序全局区(Program Global Area，PGA)

是包含单个用户或服务器数据和控制信息的内存区域。程序全局区是用户进程连接到oracle数据库并创建一个会话时，由oracle自动分配的。与sga不同，pga

并非共享区，主要用于用户在编程时存储变量和数组。该区域中 STACK SPACE 是用于存储用户会话的变量和数组的存储区域，USER

SESSION DATA 是用户会话使用的附加区域。用户会话结束pga 释放。

排序区

Sort Area 是为有排序请求的sal

语句提供内存空间，系统使用专用的内存区域进行数据排序，这部分空间就称为排序区，用户数据的排序可以分为两个一个是内存排序区，另外一个是磁盘临时段。系统优先使用内存排序区进行排序。如果内存不够，oracle自动使用磁盘临时段表空间进行排序。

要了解排序区的大小，可以查看 sort_area_size 方法如下:

参数文件：

….

Compatible = 8.1.0

Sort_area_size = 65536

Sort_area_retained_size = 65536

…..

方法二：

SQL>select name,value from v$parameter where name

like ‘%size’;

方法三：

SQL> show parameter sort_area_size

如果要了解数据库中排序区的使用情况，确认在内存排序量与磁盘排序量，可以查询数据字典 v$sysstat

SQL> select name,value from v$sysstat where name

like ‘%sort%’;

大池

大池(large pool)用于数据库备份工具—恢复管理器(rman)

大池的参数由large_pool_size 确定。

查询该值的方法有三，这里介绍其中一个方法：

SQL> show parameter large_pool_size

Java 池

参数java_pool_size 大小应该不小于20M

Oracle10g自动共享内存管理

在oracle 中 将参数STSTISTICS_LEVEL 设置为 TYPICAL(默认)或ALL，使用新参数SGA_TARGET

指出SGA内存分配总大小即可，不需要定义SHAR_POOL_SIZE DB_CACHE_SIZE

等参数，数据库服务器会自动根据需要动态分配。 注意如果没有sga_targe

，则自动共享内存管理功能被禁止，仍然可以设置每个参数。在设置了SGA_TARGET 后，其他参数默认设置为0。

自动恭喜内存管理时设置内存参数的最小值限制：

如果数据库管理员了解某一个应用所需的内存大小，就可以为内存组件指定最小值。最小值可以用相应的参数进行设置，例如：

SGA_TARGET= 256m

SHARE_POOL_SIZE= 32M

DB_CACHE_SIZE = 100M

即在自动分配时，共享池及数据缓冲区的值不得小于32m 和 100m ，其余的124m

内存将分配给其他组件。可以查询数据字典 v$sga_dynamic_components 了解各个组件的分配值。

当启动自动共享内存管理的功能后，对于自动设置的参数也可以手动设置，例如SHARE_POOL_SIZE 如果为sga

组件参数设置了比较小的值，则不会立即起作用，例如：如果设置参数SGA_TARGET = 512M ,SHARE_POOL_SIZE =

256M 而当前共享池为284M，如果dba

动态减少SHARE_POOL_SIZE的值，使之低于256M或更低的值时将不起作用。如果参数值超过当前组件值，则组件的值也随之增加，如果SHARE_POOL_SIZE的值为300M，则共享池增加到300M。

在自动管理内存时，当参数的取值增加时，则用于自动调整地额外内存就减少，反之亦然。

此外，一些sga参数必须由数据库管理员设置，系统将不进行自动调整。这些参数包括：

LOG_BUFFER

DB_KEEP_CACHE_SIZE

DB_RECYCLE_CACHE_SIZE

DB_nK_CACHE_SIZE

STREAMS_POOL_SIZE

这些参数设置后将影响到其他参数的自动分配，(可用内存变化)。

手动内存管理转到自动内存管理

如果要从手动内存管理转到自动内存管理，只需要增加参数SGA_TARGET即可。首先，计算出SGA各个参数的取值，再加上16M(fixed

SGA overhead)，即可得到SGA总大小。然后从参数文件中将内存各个组件的值取消。例如：

SHARE_POOL_SIZE = 256M

DB_CACHE_SIZE = 512M

LARGE_POOL_SIZE = 256M

LOG_BUFFER = 16M

则 SGA_TARGET = 256+512+256+16+16 =1056M

也可以查询数据字典得到：

SQL> select ( ( select sum(value)

from v$sga ) – (select current_size from v$ sga_dynamic_free_memory

) ) “sga_target” from dual;

又例如：如果一个手动管理的实例SGA_MAX_SIZE = 1200M 其中

SHARE_POOL_SIZE = 200M

DB_CACHE_SIZE = 500M

LARGE_POOL_SIZE = 200M

查询结果如下：

SQL> select sum(value) from v$sga ; 结果为1200m

SQL> select current_size from

v$sga_dynamic_free_memory ; 结果为 208M

则SGA_TARGET =1200 – 208 = 902 M

从参数文件取消SHARE_POOL_SIZE DB_CACHE_SIZE LARGE_POOL_SIZE

在自动内存管理方式下，如果数据库适用spfile

，那么数据库关闭后，系统激昂上一次数据库关闭时的状态记录下来，经过调整后的内存参数的各个组件的值将保存在参数文件中，所以如果使用自动内存管理，最好使用spfile。